package com.report.repository;

import com.report.entity.AccountingProjectDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public interface AccountingProjectDetailRepository extends JpaRepository<AccountingProjectDetail, Integer> {

    void deleteAllByProjectNoAndAuditCompanyUscc(String projectNo, String auditCompanyUscc);

    @Query(value = "SELECT IFNULL(SUM(credit_amount),0) as creditAmount, IFNULL(SUM(debit_amount),0) as debitAmount from accounting_project_detail where account_code=:accountCode AND project_code=:projectCode AND project_type_code=:projectTypeCode and audit_company_uscc=:auditCompanyUscc", nativeQuery = true)
    List<Map<String, Object>> countAmount(String accountCode, String projectCode, String projectTypeCode, String auditCompanyUscc);

    @Query("SELECT a FROM AccountingProjectDetail a WHERE a.accountCode = :accountCode AND a.auditCompanyUscc=:auditCompanyUscc order by a.voucherDate,a.voucherNumber")
    List<AccountingProjectDetail> findByAccountCodeAndAuditCompanyUscc(String accountCode, String auditCompanyUscc);

    @Query("SELECT a FROM AccountingProjectDetail a WHERE a.projectTypeCode = :projectType AND a.auditCompanyUscc=:auditCompanyUscc order by a.voucherDate,a.voucherNumber")
    List<AccountingProjectDetail> findByProjectTypeCodeAndAuditCompanyUsccOrderByVoucherDateVoucherNumber(String projectType, String auditCompanyUscc);

    @Query("SELECT a FROM AccountingProjectDetail a WHERE a.projectCode = :projectCode AND a.auditCompanyUscc=:auditCompanyUscc order by a.voucherDate,a.voucherNumber")
    List<AccountingProjectDetail> findByProjectCodeAndAuditCompanyUsccOrderByVoucherDateVoucherNumber(String projectCode, String auditCompanyUscc);

    List<AccountingProjectDetail> findByVoucherDateAndVoucherNumberAndAuditCompanyUscc(Date voucherDate, String voucherNumber, String auditCompanyUscc);

}
